#########################
#   Author : leilei.Qin #
#   Date : 2025/06/18   #
#   Id : SM2892         #
#########################

# 报表-税目明细
from datetime import datetime
import pandas as pd
from dbResps import dc
from dbResps import storeId, Log
import json


def getQueryTaxDetailReportFromDB(startTime: int, endTime: int):
    # 查询order表
    orderTableName = 'order'
    orderColumns = "tax_info as taxInfo, actual_amount as actualAmount"
    orderWhere = f'store_id = "{storeId}" and `status` = \"SETTLED\" and (UNIX_TIMESTAMP(settle_time)*1000 between {startTime} and {endTime})'
    orderResp = dc.select(tableName=orderTableName, where=orderWhere, columns=orderColumns)
    df1 = pd.DataFrame(orderResp)

    # 初始化字典用于汇总结果
    getQueryTaxDetailReport = dict()
    getQueryTaxDetailReport['totalTaxAmount'] = 0
    getQueryTaxDetailReport['taxDetailInfo'] = list()
    taxDetail = list()
    if df1.shape[0] > 0:
        for TaxInfo in orderResp:
            taxInfo = json.loads(TaxInfo['taxInfo'])
            actualAmount = TaxInfo['actualAmount']
            for taxD in taxInfo:
                taxId = f"{taxD['taxName']}_{taxD['taxRate']}"
                taxAmount = taxD['taxAmount']['amount']
                taxableAmount = actualAmount
                taxDetail.append({"taxId":taxId, "taxAmount":taxAmount, "taxableAmount":taxableAmount})
        df = pd.DataFrame(taxDetail)
        summary = df.groupby('taxId').agg(
            {"taxableAmount":"sum","taxAmount":"sum"}
        ).reset_index()
        tax_info = summary.set_index("taxId").T.to_dict()
        totalTaxAmount = sum(value['taxAmount'] for value in tax_info.values())
        lst = [{key: value} for key, value in tax_info.items()]
        getQueryTaxDetailReport['taxDetailInfo'] = sorted(lst, key=lambda x: list(x.keys())[0])
        getQueryTaxDetailReport['totalTaxAmount'] = totalTaxAmount

    return getQueryTaxDetailReport

def getHistoryTaxDetailReportFromDB(startTime: int, endTime: int):
    getHistoryTaxDetailReport = dict()
    getHistoryTaxDetailReport['totalTaxAmount'] = 0
    getHistoryTaxDetailReport['taxDetailInfo'] = list()

    # 格式化startTime
    startTime2Date = datetime.strptime(dc.timestamp2Data(startTime), "%Y/%m/%d %H:%M:%S").strftime('%Y-%m-%d %H:%M:%S')
    endTime2Date = datetime.strptime(dc.timestamp2Data(endTime), "%Y/%m/%d %H:%M:%S").strftime('%Y-%m-%d %H:%M:%S')
    # 数据库查询
    taxTableName = 'tax_detail_report_hour'
    taxColumns = "tax_name as taxName, tax_rate as taxRate, tax_amount as taxAmount, taxable_amount as taxableAmount"
    taxWhere = f'store_id = "{storeId}" and report_day between "{startTime2Date}" and "{endTime2Date}"'
    taxResp = dc.select(tableName=taxTableName, where=taxWhere, columns=taxColumns)
    tax_info = list()
    for taxInfo in taxResp:
        taxId = f"{taxInfo['taxName']}_{taxInfo['taxRate']}"
        taxAmount = taxInfo['taxAmount']
        taxableAmount = taxInfo['taxableAmount']
        tax_info.append({"taxId":taxId, "taxAmount":taxAmount, "taxableAmount":taxableAmount})
    df = pd.DataFrame(tax_info)
    taxDetail = df.groupby('taxId').agg(
        {"taxableAmount":"sum", "taxAmount":"sum"}
    ).reset_index()
    taxDetailInfo = taxDetail.set_index("taxId").T.to_dict()
    totalTaxAmount = sum(value['taxAmount'] for value in taxDetailInfo.values())
    lst = [{key: value} for key, value in taxDetailInfo.items()]
    getHistoryTaxDetailReport['taxDetailInfo'] = sorted(lst, key=lambda x: list(x.keys())[0])
    getHistoryTaxDetailReport['totalTaxAmount'] = totalTaxAmount
    return getHistoryTaxDetailReport

# startTime = 1718557200000
# endTime = 1718643599000
# result = getQueryTaxDetailReportFromDB(startTime, endTime)
# print(result)
# history_result = getHistoryTaxDetailReportFromDB(startTime, endTime)
# print(history_result)